// 产品管理模型
const { postgresPool } = require('../config/database');
const ProductCategory = require('./ProductCategory');

class Product {
    // 获取所有产品
    static async findAll(options = {}) {
        const {
            page = 1,
            limit = 20,
            search = '',
            status = '',
            market = '',
            category_id = '',
            sortBy = 'created_at',
            sortOrder = 'DESC'
        } = options;
        
        let whereConditions = [];
        let queryParams = [];
        let paramCount = 0;
        
        // 构建查询条件
        if (search) {
            paramCount++;
            whereConditions.push(`(
                sku ILIKE $${paramCount} OR 
                model ILIKE $${paramCount} OR 
                name_en ILIKE $${paramCount} OR
                name_de ILIKE $${paramCount} OR
                name_fr ILIKE $${paramCount} OR
                name_es ILIKE $${paramCount}
            )`);
            queryParams.push(`%${search}%`);
        }
        
        if (status) {
            paramCount++;
            whereConditions.push(`status = $${paramCount}`);
            queryParams.push(status);
        }
        
        if (market) {
            paramCount++;
            whereConditions.push(`market_settings->$${paramCount}->>'active' = 'true'`);
            queryParams.push(market);
        }
        
        if (category_id) {
            paramCount++;
            whereConditions.push(`category_id = $${paramCount}`);
            queryParams.push(category_id);
        }
        
        const whereClause = whereConditions.length > 0 ? 
            `WHERE ${whereConditions.join(' AND ')}` : '';
        
        // 计算分页
        const offset = (page - 1) * limit;
        paramCount++;
        queryParams.push(limit);
        paramCount++;
        queryParams.push(offset);
        
        // 构建查询（包含分类信息）
        const query = `
            SELECT 
                p.*,
                pc.name_en as category_name_en,
                pc.name_zh as category_name_zh
            FROM products p
            LEFT JOIN product_categories pc ON p.category_id = pc.id
            ${whereClause}
            ORDER BY ${sortBy} ${sortOrder}
            LIMIT $${paramCount - 1} OFFSET $${paramCount}
        `;
        
        // 获取总数
        const countQuery = `
            SELECT COUNT(*) FROM products p
            ${whereClause}
        `;
        
        try {
            const [result, countResult] = await Promise.all([
                postgresPool.query(query, queryParams),
                postgresPool.query(countQuery, whereConditions.length > 0 ? queryParams.slice(0, -2) : [])
            ]);
            
            return {
                data: result.rows,
                pagination: {
                    page: parseInt(page),
                    limit: parseInt(limit),
                    total: parseInt(countResult.rows[0].count),
                    totalPages: Math.ceil(parseInt(countResult.rows[0].count) / limit)
                }
            };
        } catch (error) {
            console.error('获取产品列表失败:', error);
            throw error;
        }
    }
    
    // 根据ID获取产品
    static async findById(id) {
        const query = 'SELECT * FROM products WHERE id = $1';
        
        try {
            const result = await postgresPool.query(query, [id]);
            return result.rows[0];
        } catch (error) {
            console.error('获取产品失败:', error);
            throw error;
        }
    }
    
    // 根据SKU获取产品
    static async findBySku(sku) {
        const query = 'SELECT * FROM products WHERE sku = $1';
        
        try {
            const result = await postgresPool.query(query, [sku]);
            return result.rows[0];
        } catch (error) {
            console.error('根据SKU获取产品失败:', error);
            throw error;
        }
    }
    
    // 创建产品
    static async create(productData) {
        const fields = [
            'sku', 'model', 'name_en', 'name_de', 'name_fr', 'name_es',
            'description_en', 'description_de', 'description_fr', 'description_es',
            'frame_material', 'frame_size', 'max_weight', 'wheel_size', 'hs_code', 'origin',
            'certifications', 'price_usd', 'price_eur', 'price_gbp', 'stock_domestic',
            'stock_overseas', 'safety_stock', 'weight', 'package_size', 'shipping_methods',
            'banned_countries', 'wholesale_tiers', 'market_settings', 'technical_docs',
            'accessories', 'status'
        ];
        
        const values = fields.map(field => productData[field]);
        const placeholders = fields.map((_, index) => `$${index + 1}`);
        
        const query = `
            INSERT INTO products (${fields.join(', ')})
            VALUES (${placeholders.join(', ')})
            RETURNING *
        `;
        
        try {
            const result = await postgresPool.query(query, values);
            return result.rows[0];
        } catch (error) {
            console.error('创建产品失败:', error);
            throw error;
        }
    }
    
    // 更新产品
    static async update(id, productData) {
        const fields = Object.keys(productData);
        const setClause = fields.map((field, index) => `${field} = $${index + 1}`).join(', ');
        const values = [...fields.map(field => productData[field]), id];
        
        const query = `
            UPDATE products 
            SET ${setClause}, updated_at = CURRENT_TIMESTAMP
            WHERE id = $${fields.length + 1}
            RETURNING *
        `;
        
        try {
            const result = await postgresPool.query(query, values);
            return result.rows[0];
        } catch (error) {
            console.error('更新产品失败:', error);
            throw error;
        }
    }
    
    // 删除产品
    static async delete(id) {
        const query = 'DELETE FROM products WHERE id = $1 RETURNING *';
        
        try {
            const result = await postgresPool.query(query, [id]);
            return result.rows[0];
        } catch (error) {
            console.error('删除产品失败:', error);
            throw error;
        }
    }
    
    // 批量更新产品
    static async batchUpdate(productIds, updateData) {
        const client = await postgresPool.connect();
        
        try {
            await client.query('BEGIN');
            
            const updatedProducts = [];
            
            for (const productId of productIds) {
                const query = `
                    UPDATE products 
                    SET ${Object.keys(updateData).map((field, index) => `${field} = $${index + 1}`).join(', ')}, updated_at = CURRENT_TIMESTAMP
                    WHERE id = $${Object.keys(updateData).length + 1}
                    RETURNING *
                `;
                
                const values = [...Object.values(updateData), productId];
                const result = await client.query(query, values);
                
                if (result.rows[0]) {
                    updatedProducts.push(result.rows[0]);
                }
            }
            
            await client.query('COMMIT');
            return updatedProducts;
        } catch (error) {
            await client.query('ROLLBACK');
            console.error('批量更新产品失败:', error);
            throw error;
        } finally {
            client.release();
        }
    }
    
    // 获取库存预警产品
    static async getLowStockAlerts() {
        const query = `
            SELECT * FROM products 
            WHERE (stock_domestic + stock_overseas) <= safety_stock
            AND status = 'active'
            ORDER BY (stock_domestic + stock_overseas) ASC
        `;
        
        try {
            const result = await postgresPool.query(query);
            return result.rows;
        } catch (error) {
            console.error('获取库存预警失败:', error);
            throw error;
        }
    }
    
    // 获取产品统计
    static async getAnalytics() {
        const query = `
            SELECT 
                COUNT(*) as total_products,
                COUNT(CASE WHEN status = 'active' THEN 1 END) as active_products,
                COUNT(CASE WHEN (stock_domestic + stock_overseas) <= safety_stock THEN 1 END) as low_stock_products,
                AVG(price_usd) as avg_price,
                SUM(stock_domestic + stock_overseas) as total_stock
            FROM products
        `;
        
        try {
            const result = await postgresPool.query(query);
            return result.rows[0];
        } catch (error) {
            console.error('获取产品统计失败:', error);
            throw error;
        }
    }
    
    // 更新库存
    static async updateStock(id, stockData) {
        const { stock_domestic, stock_overseas } = stockData;
        
        const query = `
            UPDATE products 
            SET stock_domestic = $1, stock_overseas = $2, updated_at = CURRENT_TIMESTAMP
            WHERE id = $3
            RETURNING *
        `;
        
        try {
            const result = await postgresPool.query(query, [stock_domestic, stock_overseas, id]);
            return result.rows[0];
        } catch (error) {
            console.error('更新库存失败:', error);
            throw error;
        }
    }
    
    // 搜索产品
    static async search(keyword, options = {}) {
        const { limit = 20, offset = 0 } = options;
        
        const query = `
            SELECT * FROM products
            WHERE (
                sku ILIKE $1 OR 
                model ILIKE $1 OR 
                name_en ILIKE $1 OR
                name_de ILIKE $1 OR
                name_fr ILIKE $1 OR
                name_es ILIKE $1 OR
                description_en ILIKE $1 OR
                description_de ILIKE $1 OR
                description_fr ILIKE $1 OR
                description_es ILIKE $1
            )
            AND status = 'active'
            ORDER BY created_at DESC
            LIMIT $2 OFFSET $3
        `;
        
        try {
            const result = await postgresPool.query(query, [`%${keyword}%`, limit, offset]);
            return result.rows;
        } catch (error) {
            console.error('搜索产品失败:', error);
            throw error;
        }
    }
}

module.exports = Product;